﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Common;
using Automobile.service.Model;
using System.Data;
namespace Automobile.service.DAL
{
    public class BlitemDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 分页显示盘点单
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public List<Blitem> ShowBlitem(int PageSize,int PageIndex)
        {
            string sql="select Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from (select ROW_NUMBER() over(order by Blitem_Id) as nid,Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from Blitem join WareHose on Blitem.WareHose_Id=WareHose.WareHose_Id join Product on Blitem.Product_Id=Product.Product_Id join Blitem_Type on Blitem.Blitem_Type_Id=Blitem_Type.Blitem_Type_Id) as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
            List<Blitem> list = new List<Blitem>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Blitem b = new Blitem();
                b.Blitem_Etc = item["Blitem_Etc"].ToString();
                b.WareHose_Name = item["WareHose_Name"].ToString();
                b.Blitem_date = Convert.ToDateTime(item["Blitem_date"]);
                b.Blitem_num = Convert.ToInt32(item["Blitem_num"]);
                b.Blitem_Rownum = Convert.ToInt32(item["Blitem_Rownum"]);
                b.Blitem_Stock_num = Convert.ToInt32(item["Blitem_Stock_num"]);
                b.Product_Name = item["Product_Name"].ToString();
                b.Blitem_Type_name = item["Blitem_Type_name"].ToString();
                list.Add(b);

            }
            return list;
        }
        /// <summary>
        /// 总数量
        /// </summary>
        /// <param name="Startime"></param>
        /// <param name="Endtime"></param>
        /// <returns></returns>
        public int Count(string Startime, string Endtime)
        {
            string sql = "select count(*) from Blitem ";
            if (!string.IsNullOrEmpty(Startime) && !string.IsNullOrEmpty(Endtime))
            {
                sql += "where  Blitem_date between '" + Startime + "' and '" + Endtime + "'";
            }
            return Convert.ToInt32(h.ExecScalar(sql));
        }
        /// 分页显示盘点单
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public List<Blitem> ShowBlitem_Sel(int PageSize, int PageIndex,string Startime,string Endtime)
        {
            string sql = "select Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from (select  ROW_NUMBER() over(order by Blitem_Id) as  nid,Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from Blitem join WareHose on Blitem.WareHose_Id=WareHose.WareHose_Id join Product on Blitem.Product_Id=Product.Product_Id join Blitem_Type on Blitem.Blitem_Type_Id=Blitem_Type.Blitem_Type_Id where 1=1";
            if(!string.IsNullOrEmpty(Startime)&&!string.IsNullOrEmpty(Endtime))
            {
                sql += " and Blitem_date between '" + Startime + "' and '" + Endtime + "' ";
            }
            sql += "  ) as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + " order by Blitem_date DESC ";
            List<Blitem> list = new List<Blitem>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Blitem b = new Blitem();
                b.Blitem_Etc = item["Blitem_Etc"].ToString();
                b.WareHose_Name = item["WareHose_Name"].ToString();
                b.Blitem_date = Convert.ToDateTime(item["Blitem_date"]);
                b.Blitem_num = Convert.ToInt32(item["Blitem_num"]);
                b.Blitem_Rownum = Convert.ToInt32(item["Blitem_Rownum"]);
                b.Blitem_Stock_num = Convert.ToInt32(item["Blitem_Stock_num"]);
                b.Product_Name = item["Product_Name"].ToString();
                b.Blitem_Type_name = item["Blitem_Type_name"].ToString();
                list.Add(b);

            }
            return list;
        }
    }
}